package util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import pojo.Category;
import pojo.Profile;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/10/5.
 */
public class ExcelUtil {

    static java.text.DecimalFormat formatter = new java.text.DecimalFormat("########");

    //单独sheet
    public static List<Category> getCategoryExcel(String fileName){
        List<Category> result = new ArrayList<Category>();
        //<String,Category> result = new HashMap<String, Category>();
        FileInputStream is = null;
        HSSFWorkbook workbook2003 = null;
        try {
            int emptyRow = 0;
            String cellStr;
            String orderNo = "";
            String docNum = "";
            String graphNum = "";
            String otherNum = "";
            String remark = "";
            String categoryNo = "";
            String categoryName = "";
            boolean isOneLine;
            is = new FileInputStream(fileName);// 获取文件输入流
            workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象
            HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0
            // 开始循环遍历行，表头不处理，从1开始
            for (int i = 10; i <= sheet.getLastRowNum(); i++) {
                HSSFRow row = sheet.getRow(i);// 获取行对象
                if (row == null) {// 如果为空，不处理
                    continue;
                }
                //顺序号
                HSSFCell cell = row.getCell(0);
                cellStr = getCellValue(cell);
                boolean isEmpty = isEmptyRow(row);
                if((cellStr != null && cellStr.length() > 0 && i !=10) || isEmpty){
                    isOneLine = false;
                    Category category = new Category();
                    category.setOrderNo(orderNo);
                    category.setCategoryNo(categoryNo);
                    category.setCategoryName(categoryName);
                    category.setDocNum(docNum);
                    category.setGraphNum(graphNum);
                    category.setOtherNum(otherNum);
                    category.setRemark(remark);
                    orderNo = "";
                    docNum = "";
                    graphNum = "";
                    otherNum = "";
                    remark = "";
                    categoryNo = "";
                    categoryName = "";
                    result.add(category);
                    if(isEmpty) break;
                }else{
                    isOneLine = true;
                }

                if(!isOneLine || i == 10){
                    //卷内页数-序号
                    cell = row.getCell(0);
                    orderNo = getCellValue(cell);
                    //卷内页数-文件
                    cell = row.getCell(3);
                    docNum = getCellValue(cell);
                    //卷内页数-图纸
                    cell = row.getCell(4);
                    graphNum = getCellValue(cell);
                    //卷内页数-其他
                    cell = row.getCell(5);
                    otherNum = getCellValue(cell);
                    //案卷号
                    cell = row.getCell(1);
                    categoryNo += getCellValue(cell);
                    //案卷名称
                    cell = row.getCell(2);
                    categoryName += getCellValue(cell);
                    //备注
                    cell = row.getCell(6);
                    remark += getCellValue(cell);

                }else{
                    //案卷号
                    cell = row.getCell(1);
                    categoryNo += getCellValue(cell);
                    //案卷名称
                    cell = row.getCell(2);
                    categoryName += getCellValue(cell);
                    //备注
                    cell = row.getCell(6);
                    remark += getCellValue(cell);
                }
            }
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(is!=null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return null;
    }


    //单独sheet
    public static Map<String,Category> getCategoryInfo(String fileName){
        //List<Category> result = new ArrayList<Category>();
        Map<String,Category> result = new HashMap<String, Category>();
        FileInputStream is = null;
        HSSFWorkbook workbook2003 = null;
        try {
            int emptyRow = 0;
            String cellStr;
            String orderNo = "";
            String docNum = "";
            String graphNum = "";
            String otherNum = "";
            String remark = "";
            String categoryNo = "";
            String categoryName = "";
            boolean isOneLine;
            is = new FileInputStream(fileName);// 获取文件输入流
            workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象
            HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0
            // 开始循环遍历行，表头不处理，从1开始
            for (int i = 10; i <= sheet.getLastRowNum(); i++) {
                HSSFRow row = sheet.getRow(i);// 获取行对象
                if (row == null) {// 如果为空，不处理
                    continue;
                }
                //顺序号
                HSSFCell cell = row.getCell(0);
                cellStr = getCellValue(cell);
                boolean isEmpty = isEmptyRow(row);
                if((cellStr != null && cellStr.length() > 0 && i !=10) || isEmpty){
                    isOneLine = false;
                    Category category = new Category();
                    category.setOrderNo(orderNo);
                    category.setCategoryNo(categoryNo);
                    category.setCategoryName(categoryName);
                    category.setDocNum(docNum);
                    category.setGraphNum(graphNum);
                    category.setOtherNum(otherNum);
                    category.setRemark(remark);
                    result.put(categoryNo, category);
                    orderNo = "";
                    docNum = "";
                    graphNum = "";
                    otherNum = "";
                    remark = "";
                    categoryNo = "";
                    categoryName = "";
                    if(isEmpty) break;
                }else{
                    isOneLine = true;
                }

                if(!isOneLine || i == 10){
                    //卷内页数-序号
                    cell = row.getCell(0);
                    orderNo = getCellValue(cell);
                    //卷内页数-文件
                    cell = row.getCell(3);
                    docNum = getCellValue(cell);
                    //卷内页数-图纸
                    cell = row.getCell(4);
                    graphNum = getCellValue(cell);
                    //卷内页数-其他
                    cell = row.getCell(5);
                    otherNum = getCellValue(cell);
                    //案卷号
                    cell = row.getCell(1);
                    categoryNo += getCellValue(cell);
                    //案卷名称
                    cell = row.getCell(2);
                    categoryName += getCellValue(cell);
                    //备注
                    cell = row.getCell(6);
                    remark += getCellValue(cell);

                }else{
                    //案卷号
                    cell = row.getCell(1);
                    categoryNo += getCellValue(cell);
                    //案卷名称
                    cell = row.getCell(2);
                    categoryName += getCellValue(cell);
                    //备注
                    cell = row.getCell(6);
                    remark += getCellValue(cell);
                }
            }
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(is!=null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return null;
    }

    public static Map<String, List<Profile>> getProfileInfo(String fileName){
        Map<String,List<Profile>> result = new HashMap<String, List<Profile>>();
        FileInputStream is = null;
        HSSFWorkbook workbook2003 = null;
        try {
            int emptyRow = 0;
            String categoryNo = "";
            String cellStr;
            String orderNo = "";
            String profileName = "";
            String profileNo = "";
            String page = "";
            String display = "";
            String startIndex = "";
            String endIndex = "";
            String remark = "";
            boolean isOneLine;
            is = new FileInputStream(fileName);// 获取文件输入流
            workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象
            for(int k = 0;k< workbook2003.getNumberOfSheets();k++){
                List<Profile> profileList = new ArrayList<Profile>();
                HSSFSheet sheet = workbook2003.getSheetAt(k);
                HSSFRow categoryRow = sheet.getRow(5);
                categoryNo =  categoryRow.getCell(5).getStringCellValue();
                for (int i = 9; i <= sheet.getLastRowNum(); i++) {
                    HSSFRow row = sheet.getRow(i);// 获取行对象
                    if (row == null) {// 如果为空，不处理
                        continue;
                    }
                    //顺序号
                    HSSFCell cell = row.getCell(0);
                    cellStr = getCellValue(cell);
                    boolean isEmpty = isEmptyRow2(row);
                    if((cellStr != null && cellStr.length() > 0 && i !=9) || isEmpty){
                        isOneLine = false;
                        Profile profile = new Profile();
                        profile.setOrderNo(orderNo);
                        profile.setProfileNo(profileNo);
                        profile.setProfileName(profileName);
                        profile.setPage(page);
                        profile.setDisplay(display);
                        profile.setStartIndex(startIndex);
                        profile.setEndIndex(endIndex);
                        profile.setRemark(remark);
                        orderNo = "";
                        profileName = "";
                        profileNo = "";
                        page = "";
                        display = "";
                        startIndex = "";
                        endIndex = "";
                        remark = "";
                        profileList.add(profile);
                        if(isEmpty) break;
                    }else{
                        isOneLine = true;
                    }

                    if(!isOneLine || i == 9){
                        //卷内页数-序号
                        cell = row.getCell(0);
                        orderNo = getCellValue(cell);
                        cell = row.getCell(3);
                        page = getCellValue(cell);
                        cell = row.getCell(4);
                        display = getCellValue(cell);
                        cell = row.getCell(5);
                        startIndex = getCellValue(cell);
                        cell = row.getCell(6);
                        endIndex += getCellValue(cell);

                        cell = row.getCell(1);
                        profileName += getCellValue(cell);
                        cell = row.getCell(2);
                        profileNo += getCellValue(cell);
                        //备注
                        cell = row.getCell(7);
                        remark += getCellValue(cell);

                    }else{
                        cell = row.getCell(1);
                        profileName += getCellValue(cell);
                        cell = row.getCell(2);
                        profileNo += getCellValue(cell);
                        //备注
                        cell = row.getCell(7);
                        remark += getCellValue(cell);
                    }
                }
                result.put(categoryNo, profileList);
            }
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(is!=null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return null;
    }

    private static String getCellValue(HSSFCell cell){
        if (cell == null) {// 单元格为空设置cellStr为空串
            return "";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
            return formatter.format(cell.getNumericCellValue());
        } else {// 其余按照字符串处理
            return StringUtil.getString(cell.getStringCellValue());
        }
    }

    private static boolean isEmptyRow(HSSFRow row){
        //卷内页数-序号
        HSSFCell cell = row.getCell(0);
        String a= getCellValue(cell);
        //案卷号
        cell = row.getCell(1);
        String b=getCellValue(cell);
        //案卷名称
        cell = row.getCell(2);
        String c= getCellValue(cell);
        //备注
        cell = row.getCell(6);
        String d= getCellValue(cell);

        if(StringUtils.isEmpty(a+b+c+d)){
            return true;
        }
        return false;
    }

    private static boolean isEmptyRow2(HSSFRow row){
        //卷内页数-序号
        HSSFCell cell = row.getCell(0);
        String a= getCellValue(cell);
        cell = row.getCell(1);
        String b=getCellValue(cell);
        cell = row.getCell(2);
        String c= getCellValue(cell);
        //备注
        cell = row.getCell(7);
        String d= getCellValue(cell);

        if(StringUtils.isEmpty(a+b+c+d)){
            return true;
        }
        return false;
    }
}
